package com.oracle.malldemo.util;

import com.alibaba.druid.pool.DruidDataSource;

import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;

public class DruidDBUtil {

    //声明druid连接池对象
    private static DruidDataSource dataSource = null;

    //访问数据库的小帮手
    private static String url = null;
    private static String username = null;
    private static String password = null;
    /**
     * 初始连接数
     **/
    private static int initialSize;
    /**
     * 最大活动连接数
     **/
    private static int maxActive;
    /**
     * 最小闲置连接数
     **/
    private static int minIdle;
    /**
     * 连接耗尽时最大等待获取连接时间
     **/
    private static long maxWait;

    //保证线程安全的数据库访问，一个线程只绑定一个链接对象，多次访问时同一个连接对象
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    private DruidDBUtil() {

    }

    static {
        initPool();
    }

    private static void initPool() {
        try {
            Properties pro = new Properties();
            InputStream in = DruidDBUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(in); //读取属性文档配置信息
            String driverClassName = pro.getProperty("driverClassName");
            Class.forName(driverClassName);
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            initialSize = Integer.parseInt(pro.getProperty("initialSize"));
            maxActive = Integer.parseInt(pro.getProperty("maxActive"));
            maxWait = Integer.parseInt(pro.getProperty("maxWait"));
            minIdle = Integer.parseInt(pro.getProperty("minIdle"));

            //创建druid数据源
            dataSource = new DruidDataSource();
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);

            //设置连接池中初始连接数
            dataSource.setInitialSize(initialSize);
            //设置最大连接数
            dataSource.setMaxActive(maxActive);
            //设置最小的闲置链接数
            dataSource.setMinIdle(minIdle);
            //设置最大的等待时间(等待获取链接的时间)
            dataSource.setMaxWait(maxWait);

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //由于使用了数据库连接池，我们不采用单例模式
    public static Connection getConnection() throws Exception {
        // 先从当前线程上获得链接
        Connection conn = tl.get();
        try {
            if (conn == null || conn.isClosed()) {
                //从连接池中获取连接对象
                conn = dataSource.getConnection();
                // 把连接绑定到当前线程上
                tl.set(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭所有数据库访问对象
    public static void closeAll() {
        try {

            if (DruidDBUtil.getConnection() != null && !DruidDBUtil.getConnection().isClosed()) {
                DruidDBUtil.getConnection().close();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    // 所有的增删改的方法
    public static int executeUpdate(String sql, List list) throws Exception {
        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        return pstm.executeUpdate();
    }

    // 执行更新语句返回自增主键值。
    public static Long executeUpdateWithReturnId(String sql, List list) throws Exception {
        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        pstm.executeUpdate();
        ResultSet rs  = pstm.getGeneratedKeys();
        Long id = -1L;
        //注意：这里必须把游标指向第一条记录。
        if(rs.next()){
            id = rs.getLong(1);
        }
        return id;
    }

    //执行批处理
    public static int[] executeBatch(String sql, List<List> list) throws Exception {
        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                for (int j = 0; j < list.get(i).size(); j++) {
                    pstm.setObject(j + 1, list.get(i).get(j));
                }
                pstm.addBatch();
            }
        }
        return pstm.executeBatch();
    }

    // 所有的查询的方法
    public static ResultSet executeQuery(String sql, List list) throws Exception {
        //第二个参数的意思，执行更新语句后可以获得主键
        PreparedStatement pstm = getConnection().prepareStatement(sql);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        return pstm.executeQuery();

    }


    public static void startTransaction() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void commit() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void rollback() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.rollback();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void release() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.close();
            tl.remove();// 与线程池有关，解除关系
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void endTransaction() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {

        Connection conn = DruidDBUtil.getConnection();

        if (conn != null) {
            System.out.println("MySQL连接成功！");
        } else {
            System.out.println("MySQL连接失败！");
        }
    }

}

